package com.yss.reportworld.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.List;
import java.util.StringJoiner;

import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yss.common.core.constant.RequestDataHelper;
import com.yss.common.core.enums.YssStatusEnum;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.utils.JdbcUtil;
import com.yss.common.core.utils.StringUtils;
import com.yss.common.core.utils.sql.SqlUtil;
import com.yss.common.core.utils.uuid.IdUtils;
import com.yss.common.security.utils.DictUtils;
import com.yss.common.security.utils.SecurityUtils;
import com.yss.reportworld.domain.Database;
import com.yss.reportworld.domain.DatabaseDispose;
import com.yss.reportworld.enums.ReportWorldEnum;
import com.yss.reportworld.mapper.DatabaseDisposeMapper;
import com.yss.reportworld.mapper.DatabaseMapper;
import com.yss.reportworld.util.TableInfoUtil;
import com.yss.system.api.domain.SysDictData;
import lombok.extern.slf4j.Slf4j;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import com.yss.reportworld.service.IDatabaseCheckService;
import com.yss.reportworld.mapper.DatabaseCheckMapper;
import com.yss.reportworld.domain.DatabaseCheck;
import org.springframework.util.CollectionUtils;

import javax.sql.DataSource;

/**
 * 标准层校验语句信息Service业务层处理
 *
 * @author yss
 * @date 2023-03-30
 */
@Slf4j
@Service
public class DatabaseCheckServiceImpl extends ServiceImpl<DatabaseCheckMapper, DatabaseCheck> implements IDatabaseCheckService {
    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;
    @Autowired
    private DatabaseMapper databaseMapper;
    @Autowired
    private DatabaseDisposeMapper databaseDisposeMapper;

    /**
     * 查询标准层校验语句信息列表
     *
     * @param databaseCheck 标准层校验语句信息
     * @return 标准层校验语句信息
     */
    @Override
    public List<DatabaseCheck> selectDatabaseCheckList(DatabaseCheck databaseCheck) {
        try {
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion()); // 替换表名
            QueryWrapper<DatabaseCheck> wrapper = new QueryWrapper();
            wrapper.orderByDesc("STATUS");
            wrapper.orderByAsc("TABLE_CODE,ORDER_NUM");
            if (StringUtils.isNotEmpty(databaseCheck.getTableCode())) {
                if ("true".equals(databaseCheck.getIsDataBase())) {
                    wrapper.eq("TABLE_CODE", databaseCheck.getTableCode());
                } else {
                    wrapper.like("TABLE_CODE", databaseCheck.getTableCode());
                }
            }
            if (StringUtils.isNotEmpty(databaseCheck.getVersion())) {
                wrapper.eq("VERSION", databaseCheck.getVersion());
            }
            // 导出的时候选择了数据，按照选择的数据
            if (StringUtils.isNotEmpty(databaseCheck.getIds())) {
                List<String> list = JSONArray.parseArray(databaseCheck.getIds(), String.class);
                if (StringUtils.isNotEmpty(list)) {
                    wrapper.in("ID", list);
                }
            }
            return this.list(wrapper);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 查询校验语句
     *
     * @param databaseCheck
     */
    public DatabaseCheck selectDatabaseCheckById(DatabaseCheck databaseCheck) {
        try {
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion()); // 替换表名
            return this.getById(databaseCheck.getId());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 添加校验语句
     *
     * @param databaseCheck
     */
    public void addDatabaseCheck(DatabaseCheck databaseCheck) {
        try {
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion()); // 替换表名
            databaseCheck.setId(IdUtils.fastSimpleUUID());
            databaseCheck.setCreateBy(SecurityUtils.getUsername());
            databaseCheck.setCreateTime(new Date());
            this.save(databaseCheck);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 修改校验语句
     *
     * @param databaseCheck
     */
    public void editDatabaseCheck(DatabaseCheck databaseCheck) {
        try {
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion()); // 替换表名
            databaseCheck.setUpdateTime(new Date());
            databaseCheck.setUpdateBy(SecurityUtils.getUsername());
            this.updateById(databaseCheck);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 删除校验语句
     *
     * @param databaseCheck
     */
    public void delDatabaseCheck(DatabaseCheck databaseCheck) {
        try {
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion()); // 替换表名
            this.removeByIds(databaseCheck.getIdList());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 修改顺序
     *
     * @param list
     */
    public void handleUpdateOrderNum(List<DatabaseCheck> list) {
        try {
            TableInfoUtil.handleTableVersion(list.get(0).getHisVersion()); // 替换表名
            this.updateBatchById(list);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 重新生成基础数据的校验语句，检查数据的所属模块，所属客户，所属报表信息
     *
     * @throws Exception
     */
    public void updateCheckDataBase(DatabaseCheck databaseCheck) {
        try {
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion()); // 替换表名
            QueryWrapper<Database> wrapperDatabase = new QueryWrapper<>();
            wrapperDatabase.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            List<Database> list = databaseMapper.selectList(wrapperDatabase);
            if (CollectionUtils.isEmpty(list)) {
                return;
            }
            // 获取现在最大的版本号
            List<SysDictData> sysDictDataList = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
            if (StringUtils.isEmpty(sysDictDataList)) {
                throw new BusinessException("没有获取到版本号，请稍后重试！");
            }
            String maxVersion = sysDictDataList.get(0).getDictValue();
            for (Database database : list) {
                if (StringUtils.isNull(database) || StringUtils.isEmpty(database.getCode())) {
                    continue;
                }
                if (StringUtils.oneCase(database.getCode(), ReportWorldEnum.DATABASE_NOT_CODE.getCode())) {
                    continue;
                }
                // 删除自动生成的
                QueryWrapper<DatabaseCheck> checkQueryWrapper = new QueryWrapper<>();
                checkQueryWrapper.eq("FILENAME", "voluntarilyAdd");
                checkQueryWrapper.eq("TABLE_CODE", database.getCode());
                this.remove(checkQueryWrapper);
                String sql = "SELECT * FROM " + database.getCode() + "${VERSIONSUFFIX} A WHERE  A.RW_DB_STATUS = 1 " +
                        "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} B  " +
                        " WHERE B.DATA_ID = A.RW_DB_ID  AND B.DATA_CODE = '" + database.getCode() + "')" +
                        "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} C " +
                        " WHERE C.DATA_ID = A.RW_DB_ID  AND C.DATA_CODE = '" + database.getCode() + "')" +
                        "  AND NOT EXISTS (SELECT 1 FROM REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} D " +
                        " WHERE D.DATA_ID = A.RW_DB_ID  AND D.DATA_CODE = '" + database.getCode() + "')";
                DatabaseCheck databaseCheckInfo = new DatabaseCheck();
                databaseCheckInfo.setId(IdUtils.fastSimpleUUID());
                databaseCheckInfo.setDatasql(sql/*SQLUtils.format(sql, JdbcConstants.MYSQL)*/);
                databaseCheckInfo.setCreateBy(SecurityUtils.getUsername());
                databaseCheckInfo.setCreateTime(new Date());
                databaseCheckInfo.setFilename("voluntarilyAdd");
                databaseCheckInfo.setErrorType(2);
                databaseCheckInfo.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中的数据没有对应所属模块，所属客户，所属报表信息，请检查！");
                databaseCheckInfo.setOrderNum(92);
                databaseCheckInfo.setTableCode(database.getCode());
                databaseCheckInfo.setVersion(maxVersion);
                databaseCheckInfo.setStatus(YssStatusEnum.NORMAL.getCode());
                this.save(databaseCheckInfo);

                //检查数据不能客户和报表模块同时存在
                sql = "SELECT DISTINCT A.*, B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID FROM " +
                        database.getCode() + "${VERSIONSUFFIX} A " +
                        " LEFT JOIN REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} B ON A.RW_DB_ID = B.DATA_ID " +
                        " AND B.DATA_CODE = '" + database.getCode() + "' " +
                        " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} C ON A.RW_DB_ID = C.DATA_ID " +
                        " AND C.DATA_CODE = '" + database.getCode() + "' " +
                        " LEFT JOIN REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} D ON A.RW_DB_ID = D.DATA_ID " +
                        " AND D.DATA_CODE = '" + database.getCode() + "' " +
                        " WHERE (" +
                        " (C.CUSTOMER_ID IS NOT NULL AND D.REPORT_ID IS NOT NULL) " +
                        " OR (C.CUSTOMER_ID IS NOT NULL AND B.MODULAR_ID IS NOT NULL) " +
                        " OR (D.REPORT_ID IS NOT NULL AND B.MODULAR_ID IS NOT NULL) )" +
                        " AND A.RW_DB_STATUS = 1 ";
                DatabaseCheck databaseCheckInfo2 = new DatabaseCheck();
                databaseCheckInfo2.setId(IdUtils.fastSimpleUUID());
                databaseCheckInfo2.setDatasql(sql/*SQLUtils.format(sqlPer, JdbcConstants.MYSQL)*/);
                databaseCheckInfo2.setCreateBy(SecurityUtils.getUsername());
                databaseCheckInfo2.setCreateTime(new Date());
                databaseCheckInfo2.setFilename("voluntarilyAdd");
                databaseCheckInfo2.setErrorType(2); //警告
                databaseCheckInfo2.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中数据存在所属报表，所属模块，所属客户标记了两项的数据，请检查！（所属模块，所属报表，所属模块只能标记其中一项）");
                databaseCheckInfo2.setOrderNum(93);
                databaseCheckInfo2.setStatus(YssStatusEnum.NORMAL.getCode());
                databaseCheckInfo2.setTableCode(database.getCode());
                databaseCheckInfo2.setVersion(maxVersion);
                this.save(databaseCheckInfo2);
                this.updateCheckDataBase2(database, maxVersion);
            }
        } catch (Exception ex) {
            throw new BusinessException("检查表数据信息出错：" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 基础数据检查， 所属报表，所属模块，所属客户，都必须要有标记，只能标记一项
     *
     * @throws Exception
     */
    private void updateCheckDataBase2(Database database, String version) {
        try {
            // 获取业务主键
            QueryWrapper<DatabaseDispose> wrapperDispose = new QueryWrapper<>();
            wrapperDispose.eq("TABLE_NAME", database.getCode());
            wrapperDispose.eq("IS_BUSINESS_PRIMARY", 1);
            List<DatabaseDispose> listBusinessPrimary = databaseDisposeMapper.selectList(wrapperDispose);
            if (StringUtils.isEmpty(listBusinessPrimary)) {
                return;
            }
            // 获取技术主键
            QueryWrapper<DatabaseDispose> wrapperDisposePk = new QueryWrapper<>();
            wrapperDisposePk.eq("TABLE_NAME", database.getCode());
            wrapperDisposePk.eq("IS_PRIMARY", 1);
            List<DatabaseDispose> listDisposePk = databaseDisposeMapper.selectList(wrapperDisposePk);
            if (StringUtils.isEmpty(listDisposePk)) {
                return;
            }
            StringJoiner bufferOnlyColumn = new StringJoiner(","); // 业务主键
            for (DatabaseDispose dispose : listBusinessPrimary) {
                if (StringUtils.isNull(dispose) || StringUtils.isEmpty(dispose.getProp())) {
                    continue;
                }
                if ("FCODE".equals(dispose.getProp()) && "RW_DB_T_BASE_MENU".equals(database.getCode())) {
                    bufferOnlyColumn.add(" BINARY(A.FCODE)");
                } else {
                    bufferOnlyColumn.add("A." + dispose.getProp());
                }
            }
            StringJoiner bufferPkColumn = new StringJoiner(","); // 技术主键
            for (DatabaseDispose dispose : listDisposePk) {
                if (StringUtils.isNull(dispose) || StringUtils.isEmpty(dispose.getProp())) {
                    continue;
                }
                bufferPkColumn.add("A." + dispose.getProp());
            }
            if (1 == database.getDataBaseType()) { //区分数据库 对比技术主键重复的时候使用
                bufferPkColumn.add("A.RW_DB_DATABASE_TYPE");
            }
            StringBuffer andBuffer = new StringBuffer();
            for (int i = 0; i < listBusinessPrimary.size(); i++) {
                if (0 == i) {
                    if (1 == listBusinessPrimary.get(i).getRequired()) {
                        andBuffer.append(" ON T1.`" + listBusinessPrimary.get(i).getProp() + "` = T2.`" + listBusinessPrimary.get(i).getProp() + "`");
                        continue;
                    }
                    andBuffer.append(" ON IFNULL(T1.`" + listBusinessPrimary.get(i).getProp() + "`,'') = IFNULL(T2.`" + listBusinessPrimary.get(i).getProp() + "`,'')");
                    continue;
                }
                if (1 == listBusinessPrimary.get(i).getRequired()) {
                    andBuffer.append(" AND T1.`" + listBusinessPrimary.get(i).getProp() + "` = T2.`" + listBusinessPrimary.get(i).getProp() + "`");
                    continue;
                }
                andBuffer.append(" AND IFNULL(T1.`" + listBusinessPrimary.get(i).getProp() + "`,'') = IFNULL(T2.`" + listBusinessPrimary.get(i).getProp() + "`,'')");
            }
            StringBuffer andBufferPk = new StringBuffer();
            for (int i = 0; i < listDisposePk.size(); i++) {
                if (0 == i) {
                    if (1 == listDisposePk.get(i).getRequired()) {
                        andBufferPk.append(" ON T1.`" + listDisposePk.get(i).getProp() + "` = T2.`" + listDisposePk.get(i).getProp() + "`");
                        continue;
                    }
                    andBufferPk.append(" ON IFNULL(T1.`" + listDisposePk.get(i).getProp() + "`,'') = IFNULL(T2.`" + listDisposePk.get(i).getProp() + "`,'')");
                    continue;
                }
                if (1 == listDisposePk.get(i).getRequired()) {
                    andBufferPk.append(" AND T1.`" + listDisposePk.get(i).getProp() + "` = T2.`" + listDisposePk.get(i).getProp() + "`");
                    continue;
                }
                andBufferPk.append(" AND IFNULL(T1.`" + listDisposePk.get(i).getProp() + "`,'') = IFNULL(T2.`" + listDisposePk.get(i).getProp() + "`,'')");
            }
            updateCheckDataBase3(database, version, bufferOnlyColumn);
            updateCheckDataBase4(database, version, bufferPkColumn);
            updateCheckDataBase5(database, version);
        } catch (Exception ex) {
            throw new BusinessException("检查表数据信息出错：" + ex.getMessage());
        } finally {

        }
    }

    /**
     * 业务主键 数据检查
     *
     * @param database
     * @param version
     * @param bufferOnlyColumn
     */
    private void updateCheckDataBase3(Database database, String version, StringJoiner bufferOnlyColumn) {
        String sql = "SELECT DISTINCT A.*,B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID " +
                " FROM  " + database.getCode() + "${VERSIONSUFFIX} A " +
                " LEFT JOIN REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} B ON A.RW_DB_ID = B.DATA_ID " +
                " AND B.DATA_CODE = '" + database.getCode() + "' " +
                " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} C ON A.RW_DB_ID = C.DATA_ID " +
                " AND C.DATA_CODE = '" + database.getCode() + "' " +
                " LEFT JOIN REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} D ON A.RW_DB_ID = D.DATA_ID " +
                " AND D.DATA_CODE = '" + database.getCode() + "' " +
                " WHERE A.RW_DB_STATUS = 1  " +
                // 字典的 业务主键校验的是否排除特殊字典
                ("RW_DB_TP_GL_EXHIB_DATADICT".equals(database.getCode()) ?
                        " AND A.CLASS_ID NOT IN('A0004000','A0004001','A0004002','JZ0001','RH00001','ZQ0004')" : "") +
                // 菜单的排除 新旧流程的
                ("RW_DB_T_BASE_MENU".equals(database.getCode()) ?
                        " AND A.FCODE <> 'reportSubmitted'" : "") +
                " GROUP BY " + bufferOnlyColumn + ",B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID HAVING COUNT(1) > 1";
        DatabaseCheck databaseCheck = new DatabaseCheck();
        databaseCheck.setId(IdUtils.fastSimpleUUID());
        databaseCheck.setDatasql(sql/*SQLUtils.format(sql, JdbcConstants.MYSQL)*/);
        databaseCheck.setCreateBy(SecurityUtils.getUsername());
        databaseCheck.setCreateTime(new Date());
        databaseCheck.setFilename("voluntarilyAdd");
        databaseCheck.setErrorType(2); //警告
        databaseCheck.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中相同业务主键数据的所属模块，所属客户，所属报表信息相同，请检查！（业务主键重复）");
        databaseCheck.setOrderNum(94);
        databaseCheck.setStatus(YssStatusEnum.NORMAL.getCode());
        databaseCheck.setTableCode(database.getCode());
        databaseCheck.setVersion(version);
        this.save(databaseCheck);
        // 检查所属报表和所属模块的数据业务主键是否重复，排除 标记了客户的
        sql = "SELECT DISTINCT A.* " +
                " FROM  " + database.getCode() + "${VERSIONSUFFIX} A " +
                " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} C ON A.RW_DB_ID = C.DATA_ID " +
                " AND C.DATA_CODE = '" + database.getCode() + "' " +
                " WHERE A.RW_DB_STATUS = 1   AND C.CUSTOMER_ID IS NULL " +
                // 字典的 业务主键校验的是否排除特殊字典
                ("RW_DB_TP_GL_EXHIB_DATADICT".equals(database.getCode()) ?
                        " AND A.CLASS_ID NOT IN('A0004000','A0004001','A0004002','JZ0001','RH00001','ZQ0004')" : "") +
                // 菜单的排除 新旧流程的
                ("RW_DB_T_BASE_MENU".equals(database.getCode()) ?
                        " AND A.FCODE <> 'reportSubmitted'" : "") +
                " GROUP BY " + bufferOnlyColumn + " HAVING COUNT(1) > 1";
        DatabaseCheck databaseCheck2 = new DatabaseCheck();
        databaseCheck2.setId(IdUtils.fastSimpleUUID());
        databaseCheck2.setDatasql(sql/*SQLUtils.format(sql, JdbcConstants.MYSQL)*/);
        databaseCheck2.setCreateBy(SecurityUtils.getUsername());
        databaseCheck2.setCreateTime(new Date());
        databaseCheck2.setFilename("voluntarilyAdd");
        databaseCheck2.setErrorType(2); //警告
        databaseCheck2.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中相同业务主键数据的所属模块，所属报表信息相同，请检查！（业务主键重复）");
        databaseCheck2.setOrderNum(95);
        databaseCheck2.setStatus(YssStatusEnum.NORMAL.getCode());
        databaseCheck2.setTableCode(database.getCode());
        databaseCheck2.setVersion(version);
        this.save(databaseCheck2);
    }

    /**
     * 技术主键数据检查
     *
     * @param database
     * @param version
     * @param bufferPkColumn
     */
    private void updateCheckDataBase4(Database database, String version, StringJoiner bufferPkColumn) {
        String sql = "SELECT DISTINCT A.*,B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID " +
                " FROM  " + database.getCode() + "${VERSIONSUFFIX} A " +
                " LEFT JOIN REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} B ON A.RW_DB_ID = B.DATA_ID " +
                " AND B.DATA_CODE = '" + database.getCode() + "' " +
                " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} C ON A.RW_DB_ID = C.DATA_ID " +
                " AND C.DATA_CODE = '" + database.getCode() + "' " +
                " LEFT JOIN REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} D ON A.RW_DB_ID = D.DATA_ID " +
                " AND D.DATA_CODE = '" + database.getCode() + "' " +
                " WHERE A.RW_DB_STATUS = 1   " +
                // 菜单的排除 新旧流程的
                ("RW_DB_T_BASE_MENU".equals(database.getCode()) ?
                        " AND A.FCODE <> 'reportSubmitted'" : "") +
                " GROUP BY " + bufferPkColumn + ",B.MODULAR_ID,C.CUSTOMER_ID,D.REPORT_ID HAVING COUNT(1) > 1";
        DatabaseCheck databaseCheck = new DatabaseCheck();
        databaseCheck.setId(IdUtils.fastSimpleUUID());
        databaseCheck.setDatasql(sql/*SQLUtils.format(sqlPk, JdbcConstants.MYSQL)*/);
        databaseCheck.setCreateBy(SecurityUtils.getUsername());
        databaseCheck.setCreateTime(new Date());
        databaseCheck.setFilename("voluntarilyAdd");
        databaseCheck.setErrorType(2); //警告
        databaseCheck.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中相同技术主键数据的所属模块，所属客户，所属报表信息相同，请检查！（技术主键重复）");
        databaseCheck.setOrderNum(96);
        databaseCheck.setStatus(YssStatusEnum.NORMAL.getCode());
        databaseCheck.setTableCode(database.getCode());
        databaseCheck.setVersion(version);
        this.save(databaseCheck);

        sql = "SELECT DISTINCT A.* " +
                " FROM  " + database.getCode() + "${VERSIONSUFFIX} A " +
                " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} C ON A.RW_DB_ID = C.DATA_ID " +
                " AND C.DATA_CODE = '" + database.getCode() + "' " +
                " WHERE A.RW_DB_STATUS = 1 AND C.CUSTOMER_ID IS NULL " +
                // 菜单的排除 新旧流程的
                ("RW_DB_T_BASE_MENU".equals(database.getCode()) ?
                        " AND A.FCODE <> 'reportSubmitted'" : "") +
                " GROUP BY " + bufferPkColumn + " HAVING COUNT(1) > 1";
        DatabaseCheck databaseCheck2 = new DatabaseCheck();
        databaseCheck2.setId(IdUtils.fastSimpleUUID());
        databaseCheck2.setDatasql(sql/*SQLUtils.format(sqlPk, JdbcConstants.MYSQL)*/);
        databaseCheck2.setCreateBy(SecurityUtils.getUsername());
        databaseCheck2.setCreateTime(new Date());
        databaseCheck2.setFilename("voluntarilyAdd");
        databaseCheck2.setErrorType(2); //警告
        databaseCheck2.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中相同技术主键数据的所属模块，所属报表信息相同，请检查！（技术主键重复）");
        databaseCheck2.setOrderNum(97);
        databaseCheck2.setStatus(YssStatusEnum.NORMAL.getCode());
        databaseCheck2.setTableCode(database.getCode());
        databaseCheck2.setVersion(version);
        this.save(databaseCheck2);
    }

    /**
     * 数据和表结构的 包含关系
     *
     * @param database
     * @param version
     */
    private void updateCheckDataBase5(Database database, String version) {
        // 检查表里数据对应的所属模块，包含表里的所属模块，数据模块不为空，表没有所属模块的情况也包含
        String sqlcheck1 =
                "SELECT * FROM" +
                        "(SELECT T1.*, T2.MODULAR_IDS AS MODULAR_IDS_TABLE" +
                        " FROM (" +
                        " SELECT A.*, CONCAT(B.MODULAR_ID, ';') AS MODULAR_IDS, '" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "' AS tableNameEnum" +
                        " FROM " + database.getCode() + "${VERSIONSUFFIX} A" +
                        " LEFT JOIN REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} B" +
                        " ON A.RW_DB_ID = B.DATA_ID" +
                        " AND B.DATA_CODE = '" + database.getCode() + "'" +
                        " WHERE A.RW_DB_STATUS = 1 AND B.MODULAR_ID IS NOT NULL" +
                        " ) T1" +
                        " LEFT JOIN (" +
                        " SELECT GROUP_CONCAT(D.MODULAR_ID, ';'  SEPARATOR '') AS MODULAR_IDS, C.`NAME`" +
                        " FROM REPORT_WORLD_TABLE${VERSIONSUFFIX} C LEFT JOIN REPORT_WORLD_DATA_MODULAR D" +
                        " ON C.ID = D.DATA_ID AND D.DATA_CODE = 'REPORT_TABLE_CODE' " +
                        "  WHERE C.`STATUS` = 1 AND C.`NAME` ='" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "' " +
                        "  GROUP BY C.`NAME` ) T2" +
                        " ON T1.tableNameEnum = T2.`NAME`" +
                        " ) T0" +
                        " WHERE (CHECK_STR_LOCATE(T0.MODULAR_IDS, T0.MODULAR_IDS_TABLE, ';') = 0 AND T0.MODULAR_IDS_TABLE IS NULL )";
        DatabaseCheck dbsqlcheck1 = new DatabaseCheck();
        dbsqlcheck1.setId(IdUtils.fastSimpleUUID());
        dbsqlcheck1.setDatasql(sqlcheck1/*SQLUtils.format(sqlcheck1, JdbcConstants.MYSQL)*/);
        dbsqlcheck1.setCreateBy(SecurityUtils.getUsername());
        dbsqlcheck1.setCreateTime(new Date());
        dbsqlcheck1.setFilename("voluntarilyAdd");
        dbsqlcheck1.setErrorType(2); //警告
        dbsqlcheck1.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中数据的所属模块超出了表信息的所属模块信息，请检查！");
        dbsqlcheck1.setOrderNum(98);
        dbsqlcheck1.setStatus(YssStatusEnum.NORMAL.getCode());
        dbsqlcheck1.setTableCode(database.getCode());
        dbsqlcheck1.setVersion(version);
        this.save(dbsqlcheck1);
        // 检查表里数据对应的所属报表 是否包含表对应的所属报表，表属于所属模块的不考虑
        String sqlcheck2 =
                " SELECT *" +
                        " FROM (" +
                        " SELECT T1.*, T2.REPORT_IDS AS REPORT_IDS_TABLE,T2.MODULAR_ID AS MODULAR_ID_ID" +
                        " FROM (" +
                        " SELECT A.*, CONCAT(B.REPORT_ID, ';') AS REPORT_IDS, '" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "' AS tableNameEnum" +
                        " FROM " + database.getCode() + "${VERSIONSUFFIX} A LEFT JOIN REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} B" +
                        " ON A.RW_DB_ID = B.DATA_ID AND B.DATA_CODE = '" + database.getCode() + "'" +
                        " WHERE A.RW_DB_STATUS = 1 AND B.REPORT_ID IS NOT NULL" +
                        " ) T1" +
                        " LEFT  JOIN (" +
                        " SELECT GROUP_CONCAT(D.REPORT_ID, ';'  SEPARATOR '') AS REPORT_IDS, C.`NAME`,E.MODULAR_ID" +
                        " FROM REPORT_WORLD_TABLE${VERSIONSUFFIX} C" +
                        " LEFT JOIN REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} D" +
                        " ON C.ID = D.DATA_ID AND D.DATA_CODE = 'REPORT_TABLE_CODE'" +
                        " LEFT JOIN REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} E ON C.ID = E.DATA_ID AND E.DATA_CODE = 'REPORT_TABLE_CODE' " +
                        "  WHERE C.`STATUS` = 1  AND C.`NAME`='" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "'" +
                        "   GROUP BY C.`NAME` ) T2" +
                        " ON T1.tableNameEnum = T2.`NAME`" +
                        " ) T0" +
                        " WHERE CHECK_STR_LOCATE(T0.REPORT_IDS, T0.REPORT_IDS_TABLE, ';') = 0  AND T0.MODULAR_ID_ID IS NULL";
        DatabaseCheck dbsqlcheck2 = new DatabaseCheck();
        dbsqlcheck2.setId(IdUtils.fastSimpleUUID());
        dbsqlcheck2.setDatasql(sqlcheck2/*SQLUtils.format(sqlcheck2, JdbcConstants.MYSQL)*/);
        dbsqlcheck2.setCreateBy(SecurityUtils.getUsername());
        dbsqlcheck2.setCreateTime(new Date());
        dbsqlcheck2.setFilename("voluntarilyAdd");
        dbsqlcheck2.setErrorType(2); //警告
        dbsqlcheck2.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中数据的所属报表超出了表信息的所属报表信息，请检查！");
        dbsqlcheck2.setOrderNum(99);
        dbsqlcheck2.setStatus(YssStatusEnum.NORMAL.getCode());
        dbsqlcheck2.setTableCode(database.getCode());
        dbsqlcheck2.setVersion(version);
        this.save(dbsqlcheck2);
        // 检查表里数据对应的所属客户 是否包含表对应的所属客户，表属于所属模块或者所属报表不考虑
        String sqlcheck3 =
                " SELECT * FROM" +
                        " (SELECT T1.*,T2.CUSTOMER_IDS AS CUSTOMER_IDS_TABLE,T2.MODULAR_ID AS MODULAR_ID_ID,T2.REPORT_ID AS REPORT_ID_ID FROM (" +
                        " SELECT A.*,CONCAT(B.CUSTOMER_ID,';') AS CUSTOMER_IDS,'" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "' AS tableNameEnum " +
                        " FROM " + database.getCode() + "${VERSIONSUFFIX} A" +
                        " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER B" +
                        " ON A.RW_DB_ID = B.DATA_ID AND B.DATA_CODE='" + database.getCode() + "' " +
                        "  WHERE A.RW_DB_STATUS =1  AND B.CUSTOMER_ID IS NOT NULL" +
                        " )T1" +
                        " LEFT JOIN (" +
                        " SELECT GROUP_CONCAT(D.CUSTOMER_ID,';' SEPARATOR '') as CUSTOMER_IDS,C.`NAME`,E.MODULAR_ID,F.REPORT_ID" +
                        "  FROM REPORT_WORLD_TABLE${VERSIONSUFFIX} C " +
                        " LEFT JOIN REPORT_WORLD_DATA_CUSTOMER${VERSIONSUFFIX} D ON C.ID =D.DATA_ID AND D.DATA_CODE='REPORT_TABLE_CODE'" +
                        " LEFT JOIN REPORT_WORLD_DATA_MODULAR${VERSIONSUFFIX} E ON C.ID =E.DATA_ID  AND E.DATA_CODE='REPORT_TABLE_CODE'" +
                        " LEFT JOIN REPORT_WORLD_DATA_REPORT${VERSIONSUFFIX} F ON C.ID =F.DATA_ID  AND F.DATA_CODE='REPORT_TABLE_CODE'" +
                        " WHERE C.`STATUS`=1 AND C.`NAME`='" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "' GROUP BY C.`NAME` )T2" +
                        " ON T1.tableNameEnum =T2.`NAME` )T0 WHERE CHECK_STR_LOCATE(T0.CUSTOMER_IDS, T0.CUSTOMER_IDS_TABLE ,';') = 0 " +
                        " AND T0.MODULAR_ID_ID IS NULL AND T0.REPORT_ID_ID IS NULL ";
        DatabaseCheck dbsqlcheck3 = new DatabaseCheck();
        dbsqlcheck3.setId(IdUtils.fastSimpleUUID());
        dbsqlcheck3.setDatasql(sqlcheck3/*SQLUtils.format(sqlcheck3, JdbcConstants.MYSQL)*/);
        dbsqlcheck3.setCreateBy(SecurityUtils.getUsername());
        dbsqlcheck3.setCreateTime(new Date());
        dbsqlcheck3.setFilename("voluntarilyAdd");
        dbsqlcheck3.setErrorType(2); //警告
        dbsqlcheck3.setErrorMsg(database.getName() + "【" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "") + "】表中数据的所属客户超出了表信息的所属客户信息，请检查！");
        dbsqlcheck3.setOrderNum(100);
        dbsqlcheck3.setStatus(YssStatusEnum.NORMAL.getCode());
        dbsqlcheck3.setTableCode(database.getCode());
        dbsqlcheck3.setVersion(version);
        this.save(dbsqlcheck3);
    }

    /**
     * 校验一个类型的所有数据
     * 针对页面的修改
     *
     * @param code
     * @return
     * @throws Exception
     */
    public String checkDataBaseAllByCode(String code, Connection connection, String versionSuffix) {
        PreparedStatement pStemtSelect = null;
        ResultSet rs = null;
        StringBuffer buffer = new StringBuffer();
        try {
            // 查询对应表配置的检查SQL语句
            QueryWrapper<DatabaseCheck> wrapperCheck = new QueryWrapper<>();
            wrapperCheck.eq("TABLE_CODE", code);
            wrapperCheck.orderByAsc("ORDER_NUM");
            wrapperCheck.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            List<DatabaseCheck> list = this.list(wrapperCheck);
            if (CollectionUtils.isEmpty(list)) {
                return null;
            }
            for (DatabaseCheck check : list) {
                if (null == check || StringUtils.isBlank(check.getDatasql())) {
                    continue;
                }
                String sql = check.getDatasql().replaceAll("\\$\\{VERSION\\}", "0"); // 检查的SQL语句
                sql = sql.replaceAll("\\$\\{VERSIONSUFFIX\\}", versionSuffix); // 检查的SQL语句
                // 查询
                pStemtSelect = connection.prepareStatement(sql);
                rs = pStemtSelect.executeQuery();
                while (rs.next()) {
                    if (1 == check.getErrorType()) { // 警告
                        continue;
                    }
                    buffer.append("***错误：表【" + code.replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), "")).append("校验不通过，原因【")
                            .append(check.getErrorMsg()).append("】***").append("\r\n");
                    break;
                }
                rs.close();
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException(ex.getMessage());
        } finally {
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemtSelect);
            } catch (Exception ex) {
                log.error(ex.getMessage());
                throw new BusinessException(ex.getMessage());
            }
        }
        return buffer.toString();
    }

    /**
     * 校验所有数据
     *
     * @param
     * @throws Exception
     */
    public String queryCheckDataBaseInfoAll(DatabaseCheck databaseCheck) {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            if (null == connection) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            return this.queryCheckDataBaseInfoAll(databaseCheck, connection);
        } catch (Exception ex) {
            throw new BusinessException("校验执行失败！" + ex.getMessage());
        } finally {
            try {
                JdbcUtil.close(connection);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }

    /**
     * 校验所有数据
     *
     * @param
     * @throws Exception
     */
    public String queryCheckDataBaseInfoAll(DatabaseCheck databaseCheck, Connection connection) {
        PreparedStatement pStemtSelect = null;
        ResultSet rs = null;
        String tableName = "";
        String sql = "";
        StringBuffer msgInfo = new StringBuffer();
        try {
            // 获取现在最大的版本号
            List<SysDictData> sysDictDataList = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
            if (StringUtils.isEmpty(sysDictDataList)) {
                throw new BusinessException("没有获取到版本号，请稍后重试！");
            }
            String maxVersion = sysDictDataList.get(0).getDictValue();
            StringBuffer msgInfoError = new StringBuffer();
            StringBuffer msgInfoWarn = new StringBuffer();
            TableInfoUtil.handleTableVersion(databaseCheck.getHisVersion());
            QueryWrapper<Database> wrapperDatabase = new QueryWrapper<>();
            wrapperDatabase.eq("STATUS", YssStatusEnum.NORMAL.getCode());
            if (StringUtils.isNotEmpty(databaseCheck.getTableCode())) { //提交脚本的是否调用
                wrapperDatabase.eq("CODE", databaseCheck.getTableCode());
            }
            List<Database> list = databaseMapper.selectList(wrapperDatabase);
            if (CollectionUtils.isEmpty(list)) {
                return "";
            }
            databaseCheck.setCheckPass(true);
            for (Database database : list) {
                if (StringUtils.isNull(database) || StringUtils.isBlank(database.getCode())) {
                    continue;
                }
                QueryWrapper<DatabaseCheck> wrapperCheck = new QueryWrapper<>();
                wrapperCheck.eq("TABLE_CODE", database.getCode());
                wrapperCheck.orderByAsc("ORDER_NUM");
                wrapperCheck.eq("STATUS", YssStatusEnum.NORMAL.getCode());
                List<DatabaseCheck> listDatabaseCheck = this.list(wrapperCheck);
                if (CollectionUtils.isEmpty(listDatabaseCheck)) {
                    continue;
                }
                QueryWrapper<DatabaseDispose> wrapperDispose = new QueryWrapper<>();
                wrapperDispose.eq("TABLE_NAME", database.getCode());
                wrapperDispose.eq("IS_BUSINESS_PRIMARY", 1);
                List<DatabaseDispose> listBusinessPrimary = databaseDisposeMapper.selectList(wrapperDispose);
                for (DatabaseCheck check : listDatabaseCheck) {
                    if (null == check || StringUtils.isBlank(check.getDatasql())) {
                        continue;
                    }
                    if (StringUtils.isNotEmpty(databaseCheck.getHisVersion())) {
                        sql = check.getDatasql().replaceAll("\\$\\{VERSION\\}", databaseCheck.getHisVersion()); // 检查的SQL语句
                        sql = sql.replaceAll("\\$\\{VERSIONSUFFIX\\}", TableInfoUtil.getVersionSuffix(databaseCheck.getHisVersion())); // 检查的SQL语句
                    } else {
                        sql = check.getDatasql().replaceAll("\\$\\{VERSION\\}", maxVersion); // 检查的SQL语句
                        sql = sql.replaceAll("\\$\\{VERSIONSUFFIX\\}", ""); // 检查的SQL语句
                    }
                    // 查询
                    pStemtSelect = connection.prepareStatement(sql);
                    rs = pStemtSelect.executeQuery();
                    StringBuffer buffer = new StringBuffer();
                    while (rs.next()) {
                        buffer.append("\r\n");
                        for (DatabaseDispose dispose : listBusinessPrimary) {
                            buffer.append("【").append(dispose.getProp()).append("==").append(rs.getString(dispose.getProp())).append("】");
                        }
                    }
                    rs.close();
                    if (buffer.length() > 0) {
                        if (1 == check.getErrorType()) {
                            msgInfoWarn.append("警告：表" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), ""))
                                    .append("业务主键" + buffer).append("校验不通过，原因【")
                                    .append(check.getErrorMsg()).append("】").append("\r\n");
                            continue;
                        }
                        msgInfoError.append("错误：表" + database.getCode().replace(ReportWorldEnum.RW_DB_SUFFIX.getCode(), ""))
                                .append("业务主键" + buffer).append("校验不通过，原因【")
                                .append(check.getErrorMsg()).append("】").append("\r\n");
                        continue;
                    }
                }
            }
            // 校验 结果集和ETL采集的脚本 ,先给警告
            this.checkDatabaseSql(databaseCheck, msgInfoError, connection);
            if (StringUtils.isNotEmpty(msgInfoError.toString()) || StringUtils.isNotEmpty(msgInfoWarn.toString())) {
                if (StringUtils.isEmpty(databaseCheck.getTableCode())) { //提交脚本的是否调用
                    msgInfo.append("**************************版本号【"
                            + (StringUtils.isEmpty(databaseCheck.getHisVersion()) ? maxVersion : databaseCheck.getHisVersion())
                            + "】*****************************").append("***\r\n");
                }
                if (StringUtils.isNotEmpty(msgInfoError.toString())) {
                    msgInfo.append(msgInfoError);
                    databaseCheck.setCheckPass(false);
                }
                if (StringUtils.isNotEmpty(msgInfoWarn.toString())) {
                    msgInfo.append(msgInfoWarn);
                }
                if (StringUtils.isEmpty(databaseCheck.getTableCode())) {  //提交脚本的是否调用
                    msgInfo.append("*******************************************************").append("\r\n").append("\r\n").append("\r\n");
                }
            }
        } catch (Exception ex) {
            log.error(tableName + "【" + sql + "】校验语句执行失败！" + ex.getMessage());
            throw new BusinessException(tableName + "【" + sql + "】校验语句执行失败！" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemtSelect);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return msgInfo.toString();
    }

    /**
     * 检查基础数据中的SQL 语句 语法是否正确
     */
    private void checkDatabaseSql(DatabaseCheck databaseCheck, StringBuffer msgInfoError, Connection connection) {
        ResultSet rs = null;
        PreparedStatement pStemtQuery = null;
        long startTime = System.currentTimeMillis();
        try {
            String versionSuffix = TableInfoUtil.getVersionSuffix(databaseCheck.getHisVersion());
            if (StringUtils.isEmpty(databaseCheck.getTableCode()) || "RW_DB_TC_REP_DATASET".equals(databaseCheck.getTableCode())) {
                // 检查报表结果集
                String datasetCode = "", datasetName = "", databaseType = "", id = "";
                String sql = " SELECT ID,DATASET_SQL,DATASET_CODE,DATASET_NAME,RW_DB_DATABASE_TYPE " +
                        " FROM RW_DB_TC_REP_DATASET" + versionSuffix + " WHERE RW_DB_STATUS =1  ";
                pStemtQuery = connection.prepareStatement(sql);
                rs = pStemtQuery.executeQuery();
                while (rs.next()) {
                    try {
                        String dataset_sql = rs.getString("DATASET_SQL");
                        id = rs.getString("ID");
                        databaseType = rs.getString("RW_DB_DATABASE_TYPE");
                        datasetCode = rs.getString("DATASET_CODE");
                        datasetName = rs.getString("DATASET_NAME");
                        SqlUtil.validatedReportWorldSql(dataset_sql, databaseType);
                    } catch (Exception ex) {
                        msgInfoError.append((StringUtils.isNotEmpty(databaseCheck.getTableCode())) ? "<font color='red'>" : "")
                                .append("***编号【" + id + "】数据库类型【" + databaseType + "】,报表编号【" + datasetCode + "】,报表名称【" + datasetName + "】的结果集语句" + ex.getMessage() + "***")
                                .append((StringUtils.isNotEmpty(databaseCheck.getTableCode())) ? "</font>" : "")
                                .append("\r\n");
                        databaseCheck.setCheckPass(false);
                    }
                }
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemtQuery);
            }
            if (StringUtils.isEmpty(databaseCheck.getTableCode()) || "RW_DB_ETL_JOB_DETAIL".equals(databaseCheck.getTableCode())) {
                // 检查报表结果集
                String databaseType = "", id = "";
                // 检查 ETL 脚本
                String etlNodeParamCode = "", targetResEname = "";
                String sql = "SELECT ID,SQL_STATEMENT,ETL_NODE_PARAM_CODE,TARGET_RES_ENAME,RW_DB_DATABASE_TYPE  FROM RW_DB_ETL_JOB_DETAIL" + versionSuffix + " " +
                        " WHERE RW_DB_STATUS =1 AND SQL_TYPE IN(1,2)"; // 查询插入 执行脚本 脚本
                pStemtQuery = connection.prepareStatement(sql);
                rs = pStemtQuery.executeQuery();
                while (rs.next()) {
                    try {
                        String dataset_sql = rs.getString("SQL_STATEMENT");
                        id = rs.getString("ID");
                        databaseType = rs.getString("RW_DB_DATABASE_TYPE");
                        etlNodeParamCode = rs.getString("ETL_NODE_PARAM_CODE");
                        if ("TARGET_DELETE".equals(rs.getString("ETL_NODE_PARAM_CODE"))) {
                            etlNodeParamCode = "采集前准备";
                        } else if ("TARGET_COL".equals(rs.getString("ETL_NODE_PARAM_CODE"))) {
                            etlNodeParamCode = "采集转换";
                        } else if ("TARGET_SPECIAL".equals(rs.getString("ETL_NODE_PARAM_CODE"))) {
                            etlNodeParamCode = "采集后处理";
                        }
                        targetResEname = rs.getString("TARGET_RES_ENAME");
                        SqlUtil.validatedReportWorldSql(dataset_sql, databaseType);
                    } catch (Exception ex) {
                        msgInfoError.append((StringUtils.isNotEmpty(databaseCheck.getTableCode())) ? "<font color='red'>" : "")
                                .append("***编号【" + id + "】数据库类型【" + databaseType + "】,ETL节点【" + etlNodeParamCode + "】,目标资源【" + targetResEname + "】的采集语句" + ex.getMessage() + "***")
                                .append((StringUtils.isNotEmpty(databaseCheck.getTableCode())) ? "</font>" : "")
                                .append("\r\n");
                        databaseCheck.setCheckPass(false);
                    }
                }
            }
            //任务执行总时长
            long times = System.currentTimeMillis() - startTime;
            log.info("校验所有采集语句和结果集语句总共耗时：" + times / 1000 + "秒");
        } catch (Exception ex) {
            log.error("校验结果集和采集SQL语句失败！" + ex.getMessage());
            throw new BusinessException("校验结果集和采集SQL语句失败！" + ex.getMessage());
        } finally {
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemtQuery);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
    }
}
